﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using MySql.Data.MySqlClient;

/*
 * THIS IS A _REFERENCE_ MySql IMPLEMENTATION
 * IT SHOULD _NOT_ BE USED IN A PRODUCTION ENVIRONMENT
 * THERE ARE MULTIPLE ISSUES INCLUDING PERFORMANCE TWEAKS 
 * THAT ARE REQUIRED. 
 */

namespace Scripting
{
    // Remember, multiple IAccountDatabase objects can cause RCP to select the wrong one. 
    // Ensure you've disabled MySQL in AccountDatabaseMySQL to use SQLite
    //public class AccountDatabaseSQLite  /* : IAccountDatabase */ //Comment this line, uncomment line below to use SQLite.
    public class AccountDatabaseMySQL   : IAccountDatabase   //Uncomment this line, comment line above to use SQLite.
    {
        class ActorInstance
        {
            public string Name;
            public string AccountName;
            public bool Banned;
            public bool GM;
            public int ID;
        }

        String connString =
            "server=" + DatabaseSettings.MySQLServerAddress +
            "user=" + DatabaseSettings.MySQLUserName +
            "database=" + DatabaseSettings.MySQLDatabaseNameAccounts +
            "port=" + DatabaseSettings.MySQLPort +
            "password=" + DatabaseSettings.MySQLPassword;

        Dictionary<string, AccountBase> AccountReferences = new Dictionary<string, AccountBase>();

        private int CurrentQuery = 0;
        private DateTime LastUpdate;

        private MySqlConnection sql_con1;
        private MySqlConnection sql_con2;
        private MySqlConnection sql_con3;

        AccountAddEventHandler AddDelegate;

        // Use this to speed up searches!
        static LinkedList<ActorInstance> SearchDB = new LinkedList<ActorInstance>();

        /// <summary>
        /// Initialize account database.
        /// </summary>
        public void Initialize(AccountAddEventHandler addDelegate)
        {
            AddDelegate = addDelegate;
            using (sql_con1 = new MySqlConnection(connString))
            {
                sql_con1.Open();
                MySqlCommand AcctCmd = new MySqlCommand("SELECT * FROM Accounts", sql_con1);

                using (sql_con2 = new MySqlConnection(connString))
                {
                    sql_con2.Open();
                    using (MySqlDataReader dr = AcctCmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            string Username = dr.GetValue(0).ToString();
                            string Password = dr.GetValue(1).ToString();
                            string Email = dr.GetValue(2).ToString();
                            bool GM = (bool)dr.GetValue(3);
                            bool Banned = (bool)dr.GetValue(4);

                            MySqlCommand CharCmd = new MySqlCommand("SELECT * FROM Characters WHERE Username = @Username ORDER BY ID", sql_con2);
                            CharCmd.Parameters.AddWithValue("@Username", Username);

                            using (MySqlDataReader CharDR = CharCmd.ExecuteReader())
                            {
                                while (CharDR.Read())
                                {
                                    ActorInstance AI = new ActorInstance();
                                    AI.AccountName = Username;
                                    AI.Banned = Banned;
                                    AI.GM = GM;
                                    object CharacterID = CharDR.GetValue(1);
                                    AI.ID = int.Parse(CharacterID.ToString());
                                    AI.Name = (string)CharDR.GetValue(2);
                                    SearchDB.AddLast(AI);
                                }
                            }

                            AccountReferences.Add(Username, new AccountBase(Username, Password, Email, GM, Banned));
                            addDelegate.Invoke(AccountReferences[Username], true);
                            LastUpdate = DateTime.Now;
                        }
                    }
                }
                sql_con2.Close();
                sql_con1.Close();
            }
        }


        /// <summary>
        /// Add an account to the account database
        /// </summary>
        public void Add(AccountBase account, AccountAddEventHandler completionDelegate)
        {
            // Check
            if (account == null)
            {
                completionDelegate.Invoke(account, false);
                return;
            }

            using (sql_con1 = new MySqlConnection(connString))
            {
                sql_con1.Open();
                MySqlCommand NewAcct = new MySqlCommand("INSERT INTO Accounts (Username, Password, EMail, GM, Banned) VALUES (@Username,@Password,@EMail,@GM,@Banned)", sql_con1);
                NewAcct.Parameters.AddWithValue("@Username", account.Username);
                NewAcct.Parameters.AddWithValue("@Password", account.Password);
                NewAcct.Parameters.AddWithValue("@EMail", account.Email);
                NewAcct.Parameters.AddWithValue("@GM", account.IsGM);
                NewAcct.Parameters.AddWithValue("@Banned", account.IsBanned);
                NewAcct.ExecuteNonQuery();
            }

            RCScript.Log("Account created: " + account.Username);
            AccountReferences.Add(account.Username, account);
            completionDelegate.Invoke(AccountReferences[account.Username], true);
        }

        /// <summary>
        /// Load an accounts actors.
        /// </summary>
        public void Load(AccountBase account, AccountLoadEventHandler completionDelegate)
        {
            // Check
            if (account == null)
            {
                completionDelegate.Invoke(account, null);
                return;
            }

            LoadCompleteEventArgs Args = new LoadCompleteEventArgs(account);
            using (sql_con1 = new MySqlConnection(connString))
            {
                sql_con1.Open();
                MySqlCommand CharCmd = new MySqlCommand("SELECT * FROM Characters WHERE Username = @Username ORDER BY ID", sql_con1);
                CharCmd.Parameters.AddWithValue("@Username", account.Username);

                using (MySqlDataReader CharDR = CharCmd.ExecuteReader())
                {
                    while (CharDR.Read())
                    {
                        byte[] SerializedAI = (byte[])CharDR.GetValue(3);
                        byte[] SerializedScripts = (byte[])CharDR.GetValue(4);
                        Args.ActorInstances.Add(new ActorInstanceData(SerializedAI, SerializedScripts));
                    }
                    CharDR.Close();
                }
                completionDelegate.Invoke(account, Args);
            }
            sql_con1.Close();
        }

        /// <summary>
        /// Save an accounts actors.
        /// </summary>
        public void Save(AccountBase account, int index, ActorInstanceData data)
        {
            // Check
            if (account == null)
                return;

            //Delete character
            if (data == null)
            {
                using (sql_con1 = new MySqlConnection(connString))
                {
                    sql_con1.Open();
                    MySqlCommand CharCmd = new MySqlCommand("DELETE from Characters WHERE Username = @Username AND ID = @index", sql_con1);
                    CharCmd.Parameters.AddWithValue("@Username", account.Username);
                    CharCmd.Parameters.AddWithValue("@index", index);
                    CharCmd.ExecuteNonQuery();

                    LinkedListNode<ActorInstance> AINode = SearchDB.First;
                    while (AINode != null)
                    {
                        ActorInstance AI = AINode.Value;
                        if (AI.ID == index && AI.AccountName == account.Username)
                        {
                            SearchDB.Remove(AINode);
                        }
                        AINode = AINode.Next;
                    }
                    return;
                }
            }

            byte Length = data.SerializedAI[4];
            String Name = ASCIIEncoding.ASCII.GetString(data.SerializedAI, 5, Length);

            using (sql_con1 = new MySqlConnection(connString))
            {
                sql_con1.Open();
                bool written = false;

                // Update account details which may have been edited via scripts.
                MySqlCommand NewAcct = new MySqlCommand("UPDATE Accounts SET Username = @Username, Password = @Password, EMail = @EMail, GM = @GM, Banned = @Banned WHERE Username = @Username", sql_con1);
                NewAcct.Parameters.AddWithValue("@Username", account.Username);
                NewAcct.Parameters.AddWithValue("@Password", account.Password);
                NewAcct.Parameters.AddWithValue("@EMail", account.Email);
                NewAcct.Parameters.AddWithValue("@GM", account.IsGM);
                NewAcct.Parameters.AddWithValue("@Banned", account.IsBanned);
                NewAcct.ExecuteNonQuery();

                using (sql_con2 = new MySqlConnection(connString))
                {
                    sql_con2.Open();
                    MySqlCommand CharCmd = new MySqlCommand("SELECT * FROM Characters WHERE ActorName = @ActorName", sql_con2);
                    CharCmd.Parameters.AddWithValue("@ActorName", Name);

                    using (sql_con3 = new MySqlConnection(connString))
                    {
                        sql_con3.Open();
                        using (MySqlDataReader CharDR = CharCmd.ExecuteReader())
                        {
                            while (CharDR.Read())
                            {
                                CharCmd = new MySqlCommand("UPDATE Characters SET Username = @Username, ID = @Index, ActorName = @Name, SerializedAI = @serializedAI, SerializedScripts = @serializedScripts WHERE ActorName = @Name AND Username = @UserName", sql_con3);
                                CharCmd.Parameters.AddWithValue("@UserName", account.Username);
                                CharCmd.Parameters.AddWithValue("@Index", index);
                                CharCmd.Parameters.AddWithValue("@Name", Name);
                                CharCmd.Parameters.AddWithValue("@serializedAI", data.SerializedAI);
                                CharCmd.Parameters.AddWithValue("@serializedScripts", data.SerializedScripts);
                                CharCmd.ExecuteNonQuery();
                                written = true;
                            }
                        }

                        //if there is no actor name, add it to DB (note this is not safe).
                        if (!written)
                        {
                            CharCmd = new MySqlCommand("INSERT INTO Characters (Username, ID, ActorName, SerializedAI, SerializedScripts) VALUES (@UserName,@Index,@Name,@serializedAI,@serializedScripts)", sql_con3);
                            CharCmd.Parameters.AddWithValue("@UserName", account.Username);
                            CharCmd.Parameters.AddWithValue("@Index", index);
                            CharCmd.Parameters.AddWithValue("@Name", Name);
                            CharCmd.Parameters.AddWithValue("@serializedAI", data.SerializedAI);
                            CharCmd.Parameters.AddWithValue("@serializedScripts", data.SerializedScripts);
                            CharCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            sql_con3.Close();
            sql_con2.Close();
            sql_con1.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        public void Update()
        {
            if (DatabaseSettings.AccountUpdatePollingEnabled)
            {
                int Difference = DateTime.Compare(DateTime.Now, LastUpdate.AddSeconds(DatabaseSettings.AccountsPollingDelay));
                if (Difference > 0)
                {
                    using (sql_con1 = new MySqlConnection(connString))
                    {
                        LastUpdate = DateTime.Now;
                        sql_con1.Open();

                        MySqlCommand AcctCmd = new MySqlCommand("SELECT * FROM Accounts LIMIT @Start,@End", sql_con1);
                        AcctCmd.Parameters.AddWithValue("@Start", CurrentQuery);
                        AcctCmd.Parameters.AddWithValue("@End", DatabaseSettings.AccountUpdatesPerQuery);
                        int count = 0;
                        using (MySqlDataReader dr = AcctCmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                string Username = (string)dr.GetValue(0);
                                if (AccountReferences.ContainsKey(Username))
                                {
                                    //AccountReferences[Username].Username = (string)dr.GetValue(0);
                                    AccountReferences[Username].Password = (string)dr.GetValue(1);
                                    AccountReferences[Username].Email = (string)dr.GetValue(2);
                                    AccountReferences[Username].IsGM = (bool)dr.GetValue(3);
                                    AccountReferences[Username].IsBanned = (bool)dr.GetValue(4);
                                    count++;
                                }
                                else
                                {
                                    string Password = dr.GetValue(1).ToString();
                                    string Email = dr.GetValue(2).ToString();
                                    bool GM = (bool)dr.GetValue(3);
                                    bool Banned = (bool)dr.GetValue(4);
                                    AccountReferences.Add(Username, new AccountBase(Username, Password, Email, GM, Banned));
                                    AddDelegate.Invoke(AccountReferences[Username], true);
                                    RCScript.Log("Account created: " + Username);
                                }
                            }
                        }
                        CurrentQuery += DatabaseSettings.AccountUpdatesPerQuery;
                        if (count < DatabaseSettings.AccountUpdatesPerQuery)
                        {
                            CurrentQuery = 0;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Perform a search for the given actor name to see if it exists.
        /// </summary>
        /// <param name="request">Handle of request object.</param>
        /// <param name="completionDelegate">Callback for completion of search.</param>
        public void OnActorInfoRequest(ActorInfoRequest request, AccountActorInfoRequestHandler completionDelegate)
        {
            foreach (ActorInstance AI in SearchDB)
            {
                if (AI.Name.Equals(request.ActorName, StringComparison.CurrentCultureIgnoreCase))
                {
                    completionDelegate.Invoke(request, AI.Name, AI.AccountName, AI.Banned, AI.GM);
                    return;
                }
            }

            // Failed!
            completionDelegate.Invoke(request, null, null, false, false);
        }
    }
}
